go

exec dbo.p_drop_proc_if_exists 'meta_add_decimal_column';

go

--Function of adding decimal column
create procedure meta_add_decimal_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_table_name =  dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_decimal_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);     

    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
     
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' numeric');

    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_decimal_minimum';

go

--Function of setting decimal column minimum
create procedure meta_set_decimal_minimum(
    --Column id
    @a_column_id binary(16),
    --Minimum
    @a_minimum numeric(38,5))
with execute as owner
as
begin
	set nocount on;
	declare
    --Result
    @a_result numeric(1),
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old minimum
    @a_old_minimum numeric(38,5),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_decimal_columns', 'decimal', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id,
    @a_column_name = column_name,
    @a_old_minimum = minimum
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Updating the metadata
    if @a_minimum is not null
        exec ('update meta_decimal_columns'
        + ' set minimum = ' + @a_minimum
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update meta_decimal_columns'
        + ' set minimum = null'
		+ ' where column_id = 0x' + @a_column_id_str);
     
    --There is no need to drop table view
     
	if @a_old_minimum is not null
		--Drop old constraint 
		exec dbo.p_drop_decimal_minimum_check @a_column_id;

	--Create new check
	if @a_minimum is not null
		exec dbo.p_create_decimal_minimum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_decimal_minimum_check';

go

--Function of creating decimal minimum check
create procedure p_create_decimal_minimum_check(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Table name
	@a_table_name varchar(26),
	--Minimum
	@a_minimum numeric(38,5),
	--Column name
	@a_column_name varchar(26),
	--Column id str
	@a_column_id_str varchar(32);

	set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
	
	--Reading column properties
	select @a_minimum = minimum,
    @a_column_name = column_name
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
	exec ('alter table tbl_' 
	+ @a_table_name
	+ ' add constraint n' + @a_column_id_str
	+ ' check(' + @a_column_name
	+ ' >= ' + @a_minimum + ')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_decimal_minimum_check';

go

--Function of creating decimal minimum check
create procedure p_drop_decimal_minimum_check(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Table name
	@a_table_name varchar(26),
	--Column id str
	@a_column_id_str varchar(32);

	set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
	
	--Reading column properties
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
	exec ('alter table tbl_' 
	+ @a_table_name
	+ ' drop constraint n' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_decimal_maximum';

go

--Function of setting decimal column maximum
create procedure meta_set_decimal_maximum(
    --Column id
    @a_column_id binary(16),
    --Maximum
    @a_maximum numeric(38,5))
with execute as owner
as
begin
	set nocount on;
	declare
    --Result
    @a_result numeric(1),
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old maximum
    @a_old_maximum numeric(38,5),
    --Column type
    @a_column_type varchar(30),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_decimal_columns', 'decimal', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_old_maximum = maximum,
    @a_column_name = column_name,
    @a_table_id = table_id
    from meta_decimal_columns
    where column_id = @a_column_id;
            
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Updating the metadata
    if @a_maximum is not null
        exec ('update meta_decimal_columns'
        + ' set maximum = ' + @a_maximum
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update meta_decimal_columns'
        + ' set maximum = null'
        + ' where column_id = 0x' + @a_column_id_str);
         
    --There is no need to drop table view
         
    if @a_old_maximum is not null
        --Drop old constraint 
        exec dbo.p_drop_decimal_maximum_check @a_column_id;
         
    --Create new check
    if @a_maximum is not null
        exec dbo.p_create_decimal_maximum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_decimal_maximum_check';

go

--Function of creating decimal maximum check
create procedure p_create_decimal_maximum_check(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Table name
	@a_table_name varchar(26),
	--Maximum
	@a_maximum numeric(38,5),
	--Column name
	@a_column_name varchar(26),
	--Column id str
	@a_column_id_str varchar(32);

	set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
	
	--Reading column properties
	select @a_maximum = maximum,
    @a_column_name = column_name
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
	exec ('alter table tbl_' 
	+ @a_table_name
	+ ' add constraint x' + @a_column_id_str
	+ ' check(' + @a_column_name
	+ ' <= ' + @a_maximum + ')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_decimal_maximum_check';

go

--Function of creating decimal maximum check
create procedure p_drop_decimal_maximum_check(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Table name
	@a_table_name varchar(26),
	--Column id str
	@a_column_id_str varchar(32);

	set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
	
	--Reading column properties
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
	exec ('alter table tbl_' 
	+ @a_table_name
	+ ' drop constraint x' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_decimal_precision';

go

--Function of setting decimal column precision
create procedure meta_set_decimal_precision(
    --Column id
    @a_column_id binary(16),
    --Precision
    @a_precision numeric(2))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old precision
    @a_old_precision numeric(2),
    --Old scale
    @a_old_scale numeric(2),
    --Is not null
    @a_is_not_null numeric(1),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_decimal_columns', 'decimal', @a_result output;
    
    if @a_result = 0 return;
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_old_precision = precision, @a_old_scale = scale,
    @a_is_not_null = is_not_null
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    --If there is no need for change
    if (@a_precision = @a_old_precision)
        or (@a_precision is null and @a_old_precision is null)
        return;
    
    set xact_abort on;
    begin tran;
         
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
    
    --Dropping all column indexes (otherwise SQL Server refuses to alter the column)
    exec dbo.p_delete_column_indexes @a_column_id, /*Saving the index list*/ 1;
    
    --Alter column sql
    declare @a_alter_sql varchar(max);
      
    --If precision is not specified
    if @a_precision is null
        set @a_alter_sql = 'alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' numeric';
    --If scale is not specified
    else if @a_old_scale is null
        set @a_alter_sql = 'alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' numeric(' + cast(@a_precision as varchar) + ')';
    --If precision and scale are specified
    else if @a_old_scale is not null
        set @a_alter_sql = 'alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' numeric(' + cast(@a_precision as varchar) + ','
        + cast(@a_old_scale as varchar) + ')';
        
    if @a_is_not_null = 1
		set @a_alter_sql = @a_alter_sql + ' not null';
		
	exec (@a_alter_sql);
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    --Recreating the column indexes
	exec dbo.p_recreate_column_indexes @a_column_id;
    
    --Changing the metadata
    update meta_decimal_columns
    set precision = @a_precision
    where column_id = @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_decimal_scale';

go

--Function of setting decimal column scale
create procedure meta_set_decimal_scale(
    --Column id
    @a_column_id binary(16),
    --Scale
    @a_scale numeric(2))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old precision
    @a_old_precision numeric(2),
    --Old scale
    @a_old_scale numeric(2),
    --Is not null
    @a_is_not_null numeric(1),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_decimal_columns', 'decimal', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_old_precision = precision, @a_old_scale = scale,
    @a_is_not_null = is_not_null
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    --If there is no need for change
    if (@a_scale = @a_old_scale) 
        or (@a_scale is null and @a_old_scale is null)
    begin
		commit;
        return;
    end;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    update meta_decimal_columns
    set scale = @a_scale
    where column_id = @a_column_id;
         
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
    
    --Dropping all column indexes (otherwise SQL Server refuses to alter the column)
    exec dbo.p_delete_column_indexes @a_column_id, /*Saving the index list*/ 1;
    
    --Alter column sql
    declare @a_alter_column_sql varchar(max);
    
    if @a_old_precision is not null
    begin
		--If scale is not specified
		if @a_scale is null
			set @a_alter_column_sql = 'alter table tbl_' 
			+ @a_table_name
			+ ' alter column ' + @a_column_name
			+ ' numeric(' + cast(@a_old_precision as varchar) + ')';
		--If precision and scale are specified
		else if @a_scale is not null
			set @a_alter_column_sql = 'alter table tbl_' 
			+ @a_table_name
			+ ' alter column ' + @a_column_name
			+ ' numeric(' + cast(@a_old_precision as varchar)
			+ ',' + cast(@a_scale as varchar) + ')';
			
		if @a_is_not_null = 1
		set @a_alter_column_sql = @a_alter_column_sql + ' not null';
		
		exec (@a_alter_column_sql);
    end;
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    --Recreating the column indexes
	exec dbo.p_recreate_column_indexes @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_decimal_default';

go

--Function of setting column default value
create procedure meta_set_decimal_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value numeric(38,5))
with execute as owner
as
begin
	set nocount on;
	declare
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_decimal_columns', 'decimal', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    exec dbo.p_set_numeric_default @a_column_id, @a_default_value;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_set_numeric_default';

go

--Function of setting numeric default value
create procedure p_set_numeric_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value numeric(38,5))
as
begin
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Column type
    @a_column_type varchar(30),
    --Column id str
    @a_column_id_str varchar(32),
    --Old default value
    @a_old_default_value numeric(38,5);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column type
    select @a_column_type = column_type
    from meta_table_columns
    where column_id = @a_column_id;
    
    --Reading column properties
    declare @read_col_props_sql nvarchar(500);
	set @read_col_props_sql = 'select @a_table_id_out = table_id,'
	+ ' @a_column_name_out = column_name, '
	+ ' @a_default_value_out = default_value'
	+ ' from ' + @a_column_type
	+ ' where column_id = @a_column_id';

	exec sp_executesql @read_col_props_sql,
	N'@a_column_id binary(16), @a_table_id_out binary(16) output, @a_column_name_out varchar(26) output, @a_default_value_out numeric(38,5) output', 
	@a_column_id = @a_column_id,
	@a_table_id_out = @a_table_id output,
	@a_column_name_out = @a_column_name output,
	@a_default_value_out = @a_old_default_value output;
	
	--If there is no need for change
	if (@a_old_default_value = @a_default_value) or
	(@a_old_default_value is null and @a_default_value is null)
		return;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --If there is an old default value
    if @a_old_default_value is not null
		--Dropping the old default value
		exec ('alter table tbl_' 
			+ @a_table_name
			+ ' drop constraint def_' + @a_column_id_str);
    
    --Updating the metadata
    if @a_default_value is not null
        exec ('update ' + @a_column_type
        + ' set default_value = ' + @a_default_value
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update ' + @a_column_type
        + ' set default_value = null'
        + ' where column_id = 0x' + @a_column_id_str);
    
    --If there is a new default value  
    if @a_default_value is not null
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default ' + @a_default_value + ' for ' + @a_column_name);
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_decimal_col_constrs';

go

--Function of creating all decimal column constraints
create procedure p_create_decimal_col_constrs(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Minimum
	@a_minimum numeric(38,5),
	--Maximum
	@a_maximum numeric(38,5);
	
	select @a_minimum = minimum,
	@a_maximum = maximum
	from meta_decimal_columns
	where column_id = @a_column_id;
	
	if @a_minimum is not null
		exec dbo.p_create_decimal_minimum_check @a_column_id;
	if @a_maximum is not null
		exec dbo.p_create_decimal_maximum_check @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_decimal_col_constrs';

go

--Function of dropping all decimal column constraints
create procedure p_drop_decimal_col_constrs(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Minimum
	@a_minimum numeric(38,5),
	--Maximum
	@a_maximum numeric(38,5);
	
	select @a_minimum = minimum,
	@a_maximum = maximum
	from meta_decimal_columns
	where column_id = @a_column_id;
	
	if @a_minimum is not null
		exec dbo.p_drop_decimal_minimum_check @a_column_id;
		
	if @a_maximum is not null
		exec dbo.p_drop_decimal_maximum_check @a_column_id;
end;
